package cc.messcat.dao.fupin;

import cc.messcat.common.dao.BaseDaoImpl;
import cc.messcat.common.model.Pager;
import cc.messcat.common.util.StringUtil;
import cc.messcat.entity.area.Area;
import cc.messcat.entity.fupin.Family;
import cc.messcat.entity.fupin.PoorFamily;
import cc.messcat.entity.fupin.PoorFamilyHistory;
import cc.messcat.entity.level.UserLevel;
import cc.messcat.entity.system.ApplicationProperties;
import cc.messcat.entity.system.McUser;
import cc.messcat.entity.vo.PoorFamilyVo;
import cc.messcat.util.Collections;
import cc.messcat.util.LevelUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.*;

@Repository("poorFaDao")
public class PoorFamilyDaoImpl extends BaseDaoImpl<PoorFamily>  implements PoorFamilyDao{
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List queryMainPovertyCauses( ) {
		String annual = ApplicationProperties.getAnnual();
		//初始化主要致贫的原因
		String[] reasons=new String[]{"因病","因残","因学","因灾","缺土地","缺水","缺技术","缺劳力","缺资金","交通条件落后","自身发展动力不足"};
		String baseSql="select count(*)  from  poor_family as pf  where pf.is_del='0' and poverty!='已脱贫（不再享受政策）' and poverty!='防贫户' and  pf.annual= '"+ annual+"'";
		String sql="select ";
		for(String reason:reasons){
			sql=sql+"("+baseSql+" and pf.reason like '%"+reason+"%' ) as '"+reason+"',";
		}
		sql=sql.substring(0, sql.length()-1);
		
		Session session=this.getCurrentSession();
		SQLQuery query=session.createSQLQuery(sql);
		query.setResultTransformer(Transformers.TO_LIST);
		List queryList=(List) query.list().get(0);
		
		List<Map<String,Object>> result=new ArrayList<>();
		for(int x=0;x<reasons.length;x++){
			Map map=new HashMap<String,Object>();
			map.put("name", reasons[x]);
			map.put("value", queryList.get(x));
			result.add(map);
		}
		return result;
	}
	
	
	@Override
	public List queryIncomeInfo() {
	   String sql="select a.area as name,(select ifnull(round(sum(pfi.poorFamilyIncome)/sum(pfi.poorFamilyCount),2),0) from poorfamily_income "  
	   +" as pfi where pfi.areaFatherId=a.id ) as value from area as a where  a.fatherId=3";
	   Session session=this.getCurrentSession();
	   SQLQuery query=session.createSQLQuery(sql);
	   query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	   return query.list();
	}
	
	
	
    public Area  findAreaByCode(String  code){
    	String hql="from Area where areaCode="+code.trim();
    	List result=this.find(hql);
    	if(CollectionUtils.isEmpty(result)){
    		return null;
    	}else{
    		return (Area) result.get(0);
    	}
    }
	
	
	
	/**
	 * 注意:要根据权限以及是否选择镇名来确定要显示的地名和以什么分组(镇或者村)
	 * 
	 * 如果用户的是村以上级别，就可以查看所有镇以及镇下面的所有村(如果没有选择镇，就以镇为单位统计，选择镇就以该镇下面的村为单位统计)
	 * 如果是村级别，则只能看到自己本村的情况
	 * @param user
	 * @param pageNo
	 * @param pageSize
	 * @param year
	 * @param town
	 * @param poverty
	 * @return
	 */
	@SuppressWarnings({ "unused", "rawtypes", "unchecked" })
	@Override
	public Object queryPoorPeople(McUser user,Integer pageNo, Integer pageSize, String year, String town,String poverty) {
		
        int  userLevel=user.getUserLevel().getLevel();
		
		boolean isSelectTown=StringUtil.isBlank(town)?false:true;
		boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
		boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
		String ifyear=" and pf1.ANNUAL="+year+" and f1.ANNUAL="+year+" ";
		String ifpoverty=StringUtil.isBlank(poverty)?"":" and pf1.poverty='"+poverty+"' ";
		String baseSql="SELECT COUNT(*) FROM AREA AS a1,poor_family AS pf1,family AS f1 WHERE a1.id=pf1.area_id AND pf1.id=f1.fid AND pf1.is_del='0' AND f1.is_del='0' "+ifpoverty+ifyear;
		String rowCountSql="";

		
		Area area=null;
		//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
		if(userLevel>=0 && userLevel<4){
			//如果有选择具体哪个镇
			if(isSelectTown){
				isTown=false;
				area=this.findAreaByCode(town.trim());
			}else{
				isTown=true;
				area=this.findAreaByCode("130434000000");
			}
		}
		//如果是镇和村级别
		if(userLevel==4 || userLevel==5){
			isTown=false;
			area=user.getArea();
		}
		//如果查镇
		if(isTown){
			baseSql=baseSql+" and  a1.fatherId=a.id ";
		}else{
			baseSql=baseSql+" and  a1.id=a.id ";
		}
		
		String sql="select a.area as area ,"
				+ "("+baseSql+") as 'totalPoorPeople' ,"
				+ "("+baseSql+" and f1.sex = '女') AS 'womanCount',"
				+ "("+baseSql+" and f1.nation != '汉族') AS 'nationCount',"
				+ "("+baseSql+" and f1.healthy = '残疾') AS 'disabilityCount',"
				+ "("+baseSql+" and f1.soldier = '是') AS 'serviceCount',"
				+ "("+baseSql+" and f1.low = '是') AS 'lowCount',"
				+ "("+baseSql+" and f1.large = '是') AS 'largeCount'"
				+ " from area as a where "
				+ (userLevel==5?" a.id=":" a.fatherId="+area.getId());
		
		if(ispager){
			rowCountSql="select *  from area as a  where "+(userLevel==5?" a.id=":" a.fatherId="+area.getId());
			sql=sql+" limit "+(pageNo-1)*pageSize+","+pageSize;
		}
		
		Session session=this.getCurrentSession();
		SQLQuery sqlQuery=session.createSQLQuery(sql);
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> result=sqlQuery.list();
		
		DecimalFormat df = new DecimalFormat("0.00");
		String [] keys=new String[]{"womanCount","nationCount","disabilityCount","serviceCount","lowCount","largeCount"};
		String [] names=new String[]{"womanRatio","nationRatio","disabilityCountRatio","serviceCountRatio","lowCountRatio","largeCountRatio"};
		
		for(Map map:result){
			int totalPoorPeople=Integer.valueOf(String.valueOf(map.get("totalPoorPeople")));
			for(int x=0;x<keys.length;x++){
				int value=Integer.valueOf(String.valueOf(map.get(keys[x])));
				map.put(names[x],totalPoorPeople==0 || value==0?"0%": String.format("%.2f", (new Double(value) / totalPoorPeople)*100)+"%");	
			}
		}
		
		if(ispager){
			List rowCountList=session.createSQLQuery(rowCountSql).list();
			return new Pager<>(pageSize, pageNo,rowCountList.size(), result);
		}else{
			return result;
		}
	}
	
	
	@SuppressWarnings({ "unchecked", "unused", "rawtypes" })
	@Override
	public Object queryAgeGroup(McUser user,Integer pageNo, Integer pageSize, String year, String town,String poverty) {
		
		    int  userLevel=user.getUserLevel().getLevel();
			
			boolean isSelectTown=StringUtil.isBlank(town)?false:true;
			boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
			boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
			String ifyear=" and pf1.ANNUAL="+year+" and f1.ANNUAL="+year+" ";
			String ifpoverty=StringUtil.isBlank(poverty)?"":" and pf1.poverty='"+poverty+"' ";
			String baseSql="SELECT COUNT(*) FROM AREA AS a1,poor_family AS pf1,family AS f1 WHERE a1.id=pf1.area_id AND pf1.id=f1.fid AND pf1.is_del='0' AND f1.is_del='0'  "+ifpoverty+ifyear;
			String rowCountSql="";
			
			//计算年龄的语句
			//String jsAge=" (year(now())-year(f1.BIRTH_TIME)-1) + ( DATE_FORMAT(f1.BIRTH_TIME, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) ";
			String jsAge=" (year(now())-year(f1.BIRTH_TIME)) ";
			//统计平均年龄的
			String avgAgeSql=" select FORMAT( COALESCE(sum("+jsAge+") / count(*),0),2) from area as a1 ,poor_family as pf1, family as f1 where a1.id=pf1.area_id and pf1.id=f1.fid and pf1.is_del='0' and f1.is_del='0'  "+ifpoverty+ifyear;
			
			Area area=null;
			//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
			if(userLevel>=0 && userLevel<4){
				//如果有选择具体哪个镇
				if(isSelectTown){
					isTown=false;
					area=this.findAreaByCode(town.trim());
				}else{
					isTown=true;
					area=this.findAreaByCode("130434000000");
				}
			}
			//如果是镇和村级别
			if(userLevel==4 || userLevel==5){
				isTown=false;
				area=user.getArea();
			}
			
			//如果查镇
			if(isTown){
				baseSql=baseSql+" and  a1.fatherId=a.id ";
				avgAgeSql=avgAgeSql+" and  a1.fatherId=a.id ";
			}else{
				baseSql=baseSql+" and  a1.id=a.id ";
				avgAgeSql=avgAgeSql+" and  a1.id=a.id ";
			}
			
			String sql="select a.area as area ,"
					+ "("+baseSql+") as 'totalPoorPeople' ,"
					+ "("+avgAgeSql+") as 'avgAge',"
					+ "("+baseSql+" and f1.BIRTH_TIME is not null and 16>"+jsAge+" ) as 'lt16Count',"
					+ "("+baseSql+" and f1.BIRTH_TIME is not null and 30>"+jsAge+" and "+jsAge+">=16 ) as 'ge16lt30Count',"
					+ "("+baseSql+" and f1.BIRTH_TIME is not null and 40>"+jsAge+" and "+jsAge+">=30 ) as 'ge30lt40Count',"
					+ "("+baseSql+" and f1.BIRTH_TIME is not null and 50>"+jsAge+" and "+jsAge+">=40 ) as 'ge40lt50Count',"
					+ "("+baseSql+" and f1.BIRTH_TIME is not null and 60>"+jsAge+" and "+jsAge+">=50 ) as 'ge50lt60Count',"
					+ "("+baseSql+" and f1.BIRTH_TIME is not null and "+jsAge+">=60 ) as 'ge60Count'"
					+ " from area as a where "
					+ (userLevel==5?" a.id=":" a.fatherId="+area.getId());
			
			if(ispager){
				rowCountSql="select *  from area as a  where "+(userLevel==5?" a.id=":" a.fatherId="+area.getId());
				sql=sql+" limit "+(pageNo-1)*pageSize+","+pageSize;
			}
			
			Session session=this.getCurrentSession();
			SQLQuery sqlQuery=session.createSQLQuery(sql);
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List<Map<String,Object>> result=sqlQuery.list();
			
			DecimalFormat df = new DecimalFormat("0.000");
			String [] keys=new String[]{"lt16Count","ge16lt30Count","ge30lt40Count","ge40lt50Count","ge50lt60Count","ge60Count"};
			String [] names=new String[]{"lt16Ratio","ge16lt30Ratio","ge30lt40Ratio","ge40lt50Ratio","ge50lt60Ratio","ge60Ratio"};
			
			for(Map map:result){
				int totalPoorPeople=Integer.valueOf(String.valueOf(map.get("totalPoorPeople")));
				for(int x=0;x<keys.length;x++){
					int value=Integer.valueOf(String.valueOf(map.get(keys[x])));
					map.put(names[x],totalPoorPeople==0 || value==0 ? "0%": String.format("%.2f", (new Double(value) / totalPoorPeople)*100)+"%");	
				}
			}
			
			if(ispager){
				List rowCountList=session.createSQLQuery(rowCountSql).list();
				return new Pager<>(pageSize, pageNo,rowCountList.size(), result);
			}else{
				return result;
			}
	}

	
	@SuppressWarnings("unchecked")
	@Override
	public Object queryWorkState(McUser user, Integer pageNo, Integer pageSize, String year, String town,
			String poverty) {
		    int  userLevel=user.getUserLevel().getLevel();
			
			boolean isSelectTown=StringUtil.isBlank(town)?false:true;
			boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
			boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
			String ifyear=" and pf1.ANNUAL="+year+" and f1.ANNUAL="+year+" ";
			String ifpoverty=StringUtil.isBlank(poverty)?"":" and pf1.poverty='"+poverty+"' ";
			String baseSql="SELECT COUNT(*) FROM AREA AS a1,poor_family AS pf1,family AS f1 WHERE a1.id=pf1.area_id AND pf1.id=f1.fid AND pf1.is_del='0' AND f1.is_del='0' "+ifpoverty+ifyear;
			String rowCountSql="";

			
			Area area=null;
			//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
			if(userLevel>=0 && userLevel<4){
				//如果有选择具体哪个镇
				if(isSelectTown){
					isTown=false;
					area=this.findAreaByCode(town.trim());
				}else{
					isTown=true;
					area=this.findAreaByCode("130434000000");
				}
			}
			//如果是镇和村级别
			if(userLevel==4 || userLevel==5){
				isTown=false;
				area=user.getArea();
			}
			//如果查镇
			if(isTown){
				baseSql=baseSql+" and  a1.fatherId=a.id ";
			}else{
				baseSql=baseSql+" and  a1.id=a.id ";
			}
			
			//河北省下属市
			String citysOfHeBeiSheng="'石家庄市','唐山市','秦皇岛市','邯郸市','邢台市','保定市','张家口市','承德市','沧州市','廊坊市','衡水市'";
			
			String sql="select a.area as areaName ,"
					+ "("+baseSql+") as 'totalPoorPeople' ,"
					+ "("+baseSql+" and f1.province ='河北省' and f1.village is not null and LENGTH(f1.village)>0) AS 'townCount',"
					+ "("+baseSql+" and f1.province ='河北省' and f1.county is not null and LENGTH(f1.county)>0  and (f1.village is null or LENGTH(f1.village)=0)) AS 'countyCount',"
					+ "("+baseSql+" and f1.province ='河北省' and (f1.county is null or LENGTH(f1.county)=0)  and  (f1.village is null or LENGTH(f1.village)=0 ))AS 'inProvinceCount',"
					+ "("+baseSql+" and f1.province !='河北省') AS 'outProvinceCount'"
					+ " from area as a where "
					+ (userLevel==5?" a.id=":" a.fatherId="+area.getId());
			
			if(ispager){
				rowCountSql="select *  from area as a  where "+(userLevel==5?" a.id=":" a.fatherId="+area.getId());
				sql=sql+" limit "+(pageNo-1)*pageSize+","+pageSize;
			}
			
			Session session=this.getCurrentSession();
			SQLQuery sqlQuery=session.createSQLQuery(sql);
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List<Map<String,Object>> result=sqlQuery.list();
			
			DecimalFormat df = new DecimalFormat("0.00");
			String [] keys=new String[]{"townCount","countyCount","inProvinceCount","outProvinceCount"};
			String [] names=new String[]{"townRatio","countyRatio","inProvinceCountRatio","outProvinceCountRatio"};
			
			for(Map map:result){
				int totalPoorPeople=Integer.valueOf(String.valueOf(map.get("totalPoorPeople")));
				for(int x=0;x<keys.length;x++){
					int value=Integer.valueOf(String.valueOf(map.get(keys[x])));
					map.put(names[x],totalPoorPeople==0 || value==0 ? "0%" : String.format("%.2f", (new Double(value) / totalPoorPeople)*100)+"%");	
				}
			}
			
			if(ispager){
				List rowCountList=session.createSQLQuery(rowCountSql).list();
				return new Pager<>(pageSize, pageNo,rowCountList.size(), result);
			}else{
				return result;
			}
	}
	
	
	@SuppressWarnings({ "unused", "rawtypes", "unchecked" })
	@Override
	public Object queryWorkTime(McUser user,Integer pageNo,Integer pageSize,String year,String town,String poverty) {
		
		int  userLevel=user.getUserLevel().getLevel();
		
		boolean isSelectTown=StringUtil.isBlank(town)?false:true;
		boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
		boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
		
		String ifyear=" and pf1.ANNUAL="+year+" and f1.ANNUAL="+year+" ";
		String ifpoverty=StringUtil.isBlank(poverty)?"":" and pf1.poverty='"+poverty+"' ";
		String baseSql="SELECT COUNT(*) FROM AREA AS a1,poor_family AS pf1,family AS f1 WHERE a1.id=pf1.area_id AND pf1.id=f1.fid AND pf1.is_del='0' AND f1.is_del='0' "+ifpoverty+ifyear;
		String rowCountSql="";

		
		Area area=null;
		//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
		if(userLevel>=0 && userLevel<4){
			//如果有选择具体哪个镇
			if(isSelectTown){
				isTown=false;
				area=this.findAreaByCode(town.trim());
			}else{
				isTown=true;
				area=this.findAreaByCode("130434000000");
			}
		}
		//如果是镇和村级别
		if(userLevel==4 || userLevel==5){
			isTown=false;
			area=user.getArea();
		}
		//如果查镇
		if(isTown){
			baseSql=baseSql+" and  a1.fatherId=a.id ";
		}else{
			baseSql=baseSql+" and  a1.id=a.id ";
		}
		
		String sql="select a.area as areaName ,"
				+ "("+baseSql+") as 'totalPoorPeople' ,"
				+ "("+baseSql+" and f1.WORK_TIME = '1'  ) AS 'month1',"
				+ "("+baseSql+" and f1.WORK_TIME = '2'  ) AS 'month2',"
				+ "("+baseSql+" and f1.WORK_TIME = '3'  ) AS 'month3',"
				+ "("+baseSql+" and f1.WORK_TIME = '4'  ) AS 'month4',"
				+ "("+baseSql+" and f1.WORK_TIME = '5'  ) AS 'month5',"
				+ "("+baseSql+" and f1.WORK_TIME = '6'  ) AS 'month6',"
				+ "("+baseSql+" and f1.WORK_TIME = '7'  ) AS 'month7',"
				+ "("+baseSql+" and f1.WORK_TIME = '8'  ) AS 'month8',"
				+ "("+baseSql+" and f1.WORK_TIME = '9'  ) AS 'month9',"
				+ "("+baseSql+" and f1.WORK_TIME = '10' ) AS 'month10',"
				+ "("+baseSql+" and f1.WORK_TIME = '11' ) AS 'month11',"
				+ "("+baseSql+" and f1.WORK_TIME = '12' ) AS 'month12'"
				+ " from area as a where "
				+ (userLevel==5?" a.id=":" a.fatherId="+area.getId());
		
		if(ispager){
			rowCountSql="select *  from area as a  where "+(userLevel==5?" a.id=":" a.fatherId="+area.getId());
			sql=sql+" limit "+(pageNo-1)*pageSize+","+pageSize;
		}
		
		Session session=this.getCurrentSession();
		SQLQuery sqlQuery=session.createSQLQuery(sql);
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> result=sqlQuery.list();
		
		DecimalFormat df = new DecimalFormat("0.00");
		String [] keys=new String[]{"month1","month2","month3",
				"month4","month5","month6",
				"month7","month8","month9",
				"month10","month11","month12"};
		
		String [] names=new String[]{"month1Ratio","month2Ratio","month3Ratio",
				"month4Ratio","month5Ratio","month6Ratio",
				"month7Ratio","month8Ratio","month9Ratio",
				"month10Ratio","month11Ratio","month12Ratio"};
		
		for(Map map:result){
			int totalPoorPeople=Integer.valueOf(String.valueOf(map.get("totalPoorPeople")));
			for(int x=0;x<keys.length;x++){
				int value=Integer.valueOf(String.valueOf(map.get(keys[x])));
				map.put(names[x],totalPoorPeople==0 || value==0 ? "0%" : String.format("%.2f", (new Double(value)/totalPoorPeople)*100)+"%");	
			}
		}
		
		if(ispager){
			List rowCountList=session.createSQLQuery(rowCountSql).list();
			return new Pager<>(pageSize, pageNo,rowCountList.size(), result);
		}else{
			return result;
		}
	}
	
	
	@Override
	@SuppressWarnings({ "unused", "unchecked", "rawtypes" })
	public Object queryInternalStudent(McUser user,Integer pageNo,Integer pageSize,String year,String town,String poverty) {
		    int  userLevel=user.getUserLevel().getLevel();
			
			boolean isSelectTown=StringUtil.isBlank(town)?false:true;
			boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
			boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
			String ifyear=" and pf1.ANNUAL="+year+" and f1.ANNUAL="+year+" ";
			String ifpoverty=StringUtil.isBlank(poverty)?"":" and pf1.poverty='"+poverty+"' ";
			String baseSql="SELECT COUNT(*) FROM AREA AS a1,poor_family AS pf1,family AS f1 WHERE a1.id=pf1.area_id AND pf1.id=f1.fid AND pf1.is_del='0' AND f1.is_del='0' "+ifpoverty+ifyear;
			String rowCountSql="";

			
			Area area=null;
			//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
			if(userLevel>=0 && userLevel<4){
				//如果有选择具体哪个镇
				if(isSelectTown){
					isTown=false;
					area=this.findAreaByCode(town.trim());
				}else{
					isTown=true;
					area=this.findAreaByCode("130434000000");
				}
			}
			//如果是镇和村级别
			if(userLevel==4 || userLevel==5){
				isTown=false;
				area=user.getArea();
			}
			//如果查镇
			if(isTown){
				baseSql=baseSql+" and  a1.fatherId=a.id ";
			}else{
				baseSql=baseSql+" and  a1.id=a.id ";
			}
			
			String sql="select a.area as areaName ,"
					+ "("+baseSql+") as 'totalPoorPeople' ,"
					+ "("+baseSql+" and f1.SCHOOL='非在校生') AS 'noStudentCount',"
					+ "("+baseSql+" and f1.SCHOOL='学前教育' ) AS 'xqjyCount',"
					+ "("+baseSql+" and f1.SCHOOL='小学') AS 'xiaoxueCount',"
					
					+ "("+baseSql+" and f1.SCHOOL='七年级') AS '7nianjiCount',"
					+ "("+baseSql+" and f1.SCHOOL='八年级') AS '8nianjiCount',"
					+ "("+baseSql+" and f1.SCHOOL='九年级') AS '9nianjiCount',"
					
					+ "("+baseSql+" and f1.SCHOOL='高中一年级') AS 'gaozhong1Count',"
					+ "("+baseSql+" and f1.SCHOOL='高中二年级') AS 'gaozhong2Count',"
					+ "("+baseSql+" and f1.SCHOOL='高中三年级') AS 'gaozhong3Count',"
					
					+ "("+baseSql+" and f1.SCHOOL='中职一年级') AS 'zhongzhi1Count',"
					+ "("+baseSql+" and f1.SCHOOL='中职二年级') AS 'zhongzhi2Count',"
					+ "("+baseSql+" and f1.SCHOOL='中职三年级') AS 'zhongzhi3Count',"
					
					+ "("+baseSql+" and f1.SCHOOL='高职一年级') AS 'gaozhi1Count',"
					+ "("+baseSql+" and f1.SCHOOL='高职二年级') AS 'gaozhi2Count',"
					+ "("+baseSql+" and f1.SCHOOL='高职三年级') AS 'gaozhi3Count',"
					
                    + "("+baseSql+" and f1.SCHOOL='大专及以上') AS 'dazhuanCount'"
					+ " from area as a where "
					+ (userLevel==5?" a.id=":" a.fatherId="+area.getId());
			
			if(ispager){
				rowCountSql="select *  from area as a  where "+(userLevel==5?" a.id=":" a.fatherId="+area.getId());
				sql=sql+" limit "+(pageNo-1)*pageSize+","+pageSize;
			}
			
			Session session=this.getCurrentSession();
			SQLQuery sqlQuery=session.createSQLQuery(sql);
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List<Map<String,Object>> result=sqlQuery.list();
			
			DecimalFormat df = new DecimalFormat("0.00");
			String [] keys=new String[]{"noStudentCount","xqjyCount","xiaoxueCount",
					"7nianjiCount","8nianjiCount","9nianjiCount",
					"gaozhong1Count","gaozhong2Count","gaozhong3Count",
					"zhongzhi1Count","zhongzhi2Count","zhongzhi3Count",
					"gaozhi1Count","gaozhi2Count","gaozhi3Count","dazhuanCount"};
			
			String [] names=new String[]{"noStudentRatio","xqjyRatio","xiaoxueRatio",
					"7nianjiRatio","8nianjiRatio","9nianjiRatio",
					"gaozhong1Ratio","gaozhong2Ratio","gaozhong3Ratio",
					"zhongzhi1Ratio","zhongzhi2Ratio","zhongzhi3Ratio",
					"gaozhi1Ratio","gaozhi2Ratio","gaozhi3Ratio","dazhuanRatio"};
			
			for(Map map:result){
				int totalPoorPeople=Integer.valueOf(String.valueOf(map.get("totalPoorPeople")));
				for(int x=0;x<keys.length;x++){
					int value=Integer.valueOf(String.valueOf(map.get(keys[x])));
					map.put(names[x],totalPoorPeople==0 || value==0 ? "0%" : String.format("%.2f", (new Double(value) / totalPoorPeople)*100)+"%");	
				}
			}
			
			if(ispager){
				List rowCountList=session.createSQLQuery(rowCountSql).list();
				return new Pager<>(pageSize, pageNo,rowCountList.size(), result);
			}else{
				return result;
			}
	}
	
	
	@SuppressWarnings({ "unchecked", "unused", "rawtypes" })
	@Override
	public Object queryPoorStatus(McUser user,Integer pageNo,Integer pageSize,String year,String town,String poverty) {
		 int  userLevel=user.getUserLevel().getLevel();
			
			boolean isSelectTown=StringUtil.isBlank(town)?false:true;
			boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
			boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
			String ifyear=" and pf1.ANNUAL="+year+" and f1.ANNUAL="+year+" ";
			String ifpoverty=StringUtil.isBlank(poverty)?"":" and pf1.poverty='"+poverty+"' ";
			//这条语句是统计人数的
			String baseSql_PeopleCount="SELECT COUNT(*) FROM AREA AS a1,poor_family AS pf1,family AS f1 WHERE a1.id=pf1.area_id AND pf1.id=f1.fid AND pf1.is_del='0' AND f1.is_del='0' "+ifpoverty+ifyear;
			//这条语句是统计户数的
			String baseSql_Households="SELECT COUNT(*) FROM AREA AS a1,poor_family AS pf1  WHERE a1.id=pf1.area_id  AND pf1.is_del='0'  "+ifpoverty+"  and pf1.ANNUAL="+year+" ";
			String rowCountSql="";

			
			Area area=null;
			//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
			if(userLevel>=0 && userLevel<4){
				//如果有选择具体哪个镇
				if(isSelectTown){
					isTown=false;
					area=this.findAreaByCode(town.trim());
				}else{
					isTown=true;
					area=this.findAreaByCode("130434000000");
				}
			}
			//如果是镇和村级别
			if(userLevel==4 || userLevel==5){
				isTown=false;
				area=user.getArea();
			}
			
			//如果查镇
			if(isTown){
				baseSql_PeopleCount=baseSql_PeopleCount+" and  a1.fatherId=a.id ";
				baseSql_Households=baseSql_Households+" and  a1.fatherId=a.id ";
			}else{
				baseSql_PeopleCount=baseSql_PeopleCount+" and  a1.id=a.id ";
				baseSql_Households=baseSql_Households+" and  a1.id=a.id ";
			}
			
			String sql="select a.area as areaName ,"
					+ "("+baseSql_Households+" ) as 'totalHouseholds' ,"
					+ "("+baseSql_PeopleCount+") as 'totalPoorPeople' ,"
					+ "("+baseSql_Households+"  and pf1.attribute = '一般贫困户' ) AS 'yiBanHouseholds',"
					+ "("+baseSql_Households+"  and pf1.attribute = '低保贫困户' ) AS 'diBaoHouseholds',"
					+ "("+baseSql_Households+"  and pf1.attribute = '五保贫困户' ) AS 'wuBaoHouseholds',"
					+ "("+baseSql_PeopleCount+" and pf1.attribute = '一般贫困户' ) AS 'yiBanPeopleCount',"
					+ "("+baseSql_PeopleCount+" and pf1.attribute = '低保贫困户' ) AS 'diBaoPeopleCount',"
					+ "("+baseSql_PeopleCount+" and pf1.attribute = '五保贫困户' ) AS 'wuBaoPeopleCount'"
					+ " from area as a where "
					+ (userLevel==5?" a.id=":" a.fatherId="+area.getId());
			
			if(ispager){
				rowCountSql="select *  from area as a  where "+(userLevel==5?" a.id=":" a.fatherId="+area.getId());
				sql=sql+" limit "+(pageNo-1)*pageSize+","+pageSize;
			}
			
			Session session=this.getCurrentSession();
			SQLQuery sqlQuery=session.createSQLQuery(sql);
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List<Map<String,Object>> result=sqlQuery.list();
			
			DecimalFormat df = new DecimalFormat("0.00");
			String [] keys=new String[]{"yiBanHouseholds","diBaoHouseholds","wuBaoHouseholds","yiBanPeopleCount","diBaoPeopleCount","wuBaoPeopleCount"};
			String [] names=new String[]{"yiBanHouseholdsRatio","diBaoHouseholdsRatio","wuBaoHouseholdsRatio","yiBanPeopleCountRatio","diBaoPeopleCountRatio","wuBaoPeopleCountRatio"};
			
			for(Map map:result){
				int totalHouseholds=Integer.valueOf(String.valueOf(map.get("totalHouseholds")));
				int totalPoorPeople=Integer.valueOf(String.valueOf(map.get("totalPoorPeople")));
				for(int x=0;x<keys.length;x++){
					String key=keys[x];
					int value=Integer.valueOf(String.valueOf(map.get(key)));
			
						
					if(key.contains("Households")){
						map.put(names[x],totalHouseholds==0 || value==0 ? "0%" :String.format("%.2f", (new Double(value) / totalHouseholds)*100)+"%");	
					}else{
						map.put(names[x],totalPoorPeople==0 || value==0 ? "0%" : String.format("%.2f", (new Double(value) / totalPoorPeople)*100)+"%");	
					}	
					
				}
			}
			
			if(ispager){
				List rowCountList=session.createSQLQuery(rowCountSql).list();
				return new Pager<>(pageSize, pageNo,rowCountList.size(), result);
			}else{
				return result;
			}
	}
	
	
    
	@Override
	public Object queryMainPovertyCauses(McUser user, Integer pageNo, Integer pageSize, String year, String town,
			String poverty) {
        int  userLevel=user.getUserLevel().getLevel();
		
		boolean isSelectTown=StringUtil.isBlank(town)?false:true;
		boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
		boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
		
		String  ifpoverty=StringUtil.isBlank(poverty)?"":" and  pf1.poverty='"+poverty.trim()+"' ";
		String  ifyear=" and pf1.annual="+year.trim()+" ";
		String baseSql=" select count(*) from area as a1,poor_family as pf1  where a1.id=pf1.area_id  and pf1.is_del='0' "+ifpoverty+ifyear;
		
		
		Area area=null;
		//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
		if(userLevel>=0 && userLevel<4){
			//如果有选择具体哪个镇
			if(isSelectTown){
				isTown=false;
				area=this.findAreaByCode(town.trim());
			}else{
				isTown=true;
				area=this.findAreaByCode("130434000000");
			}
		}
		//如果是镇和村级别
		if(userLevel==4 || userLevel==5){
			isTown=false;
			area=user.getArea();
		}
		
		//如果是查镇的
		if(isTown){
			baseSql=baseSql+" and a1.fatherId=a.id  ";
			
		}else{
			baseSql=baseSql+" and a1.id=a.id  ";
		}
		
		String sqlQuery="select a.area as areaName,"
					+ "("+baseSql+" ) as 'poorFamilyCount' , "
					+ "("+baseSql+" and pf1.reason like '因病%' ) as 'illnessCount' ,"
					+ "("+baseSql+" and pf1.reason like '因残%' ) as 'deformityCount' ,"
					+ "("+baseSql+" and pf1.reason like '因学%' ) as 'studyCount' ,"
					+ "("+baseSql+" and pf1.reason like '因灾%' ) as 'disasterCount' ,"
					+ "("+baseSql+" and pf1.reason like '缺土地%' ) as 'landCount' ,"
					+ "("+baseSql+" and pf1.reason like '缺水%' ) as 'waterCount' ,"
				    + "("+baseSql+" and pf1.reason like '缺技术%' ) as 'technologyCount' ,"
				    + "("+baseSql+" and pf1.reason like '缺劳力%' ) as 'labourCount' ,"
					+ "("+baseSql+" and pf1.reason like '缺资金' ) as 'capitalCount' ,"
				    + "("+baseSql+" and pf1.reason like '交通条件落后%' ) as 'trafficCount' ,"
				    + "("+baseSql+" and pf1.reason like '自身发展动力不足%' ) as 'developCount' "
					+ "  from  area  as  a  where "
				    +(userLevel==5?" a.id=":" a.fatherId=")+area.getId();
		
		
		if(ispager){
			
		   sqlQuery=sqlQuery+" limit "+(pageNo-1)*pageSize+","+pageSize;	
		}
		
		Session session=this.getCurrentSession();
		SQLQuery query=session.createSQLQuery(sqlQuery);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> result=query.list();
		
		DecimalFormat df = new DecimalFormat("0.00");
		String [] keys=new String[]{"illnessCount","deformityCount","studyCount","disasterCount","landCount"
				,"waterCount","technologyCount","labourCount","capitalCount","trafficCount","developCount"};
		
		String [] name=new String[]{"illnessRatio","deformityRatio","studyRatio","disasterRatio","landRatio"
				,"waterRatio","technologyRatio","labourRatio","capitalRatio","trafficRatio","developRatio"};
		
		for(Map<String,Object> map:result){
			
			int poorFamilyCount=Integer.valueOf(String.valueOf(map.get("poorFamilyCount")));
			
			for(int x=0;x<keys.length;x++){
				int value=Integer.valueOf(String.valueOf(map.get(keys[x])));
				map.put(name[x],poorFamilyCount==0 || value==0 ? "0%" : String.format("%.2f", (new Double(value) / poorFamilyCount)*100)+"%");	
			}
		}
		
		if(ispager){
			String rowCountSql="select * from  area as a where "+(userLevel==5?" a.id=":" a.fatherId=")+area.getId();
			List list=session.createSQLQuery(rowCountSql).list();
			return new Pager<>(pageSize, pageNo, list.size(), result);
		}
		return result;
	}
	
	
	
	@SuppressWarnings("unused")
	@Override
	public Object queryOtherPovertyCauses(McUser user, Integer pageNo, Integer pageSize, final String year, String town,
			String poverty) {
		int  userLevel=user.getUserLevel().getLevel();
		
		boolean isSelectTown=StringUtil.isBlank(town)?false:true;
		boolean ispager=(pageNo!=null && pageSize!=null && pageNo>=1 && pageSize>=1)?true:false;
		boolean isTown=false;//用来标识是以镇为单位查询，还是以村为单位查询，true表示查询镇，false表示查村
		
		String  ifpoverty=StringUtil.isBlank(poverty)?"":" and  pf1.poverty='"+poverty.trim()+"' ";
		String  ifyear=" and pf1.annual="+year.trim()+" ";
		String baseSql=" select count(*) from area as a1,poor_family as pf1  where a1.id=pf1.area_id  and pf1.is_del='0' "+ifpoverty+ifyear;
		String sqlQuery="";
		String baseSql_rowCount="";
		
		Area area=null;
		//如果是镇以上的级别并且没有选择具体的镇，就查询镇，其他情况都是查村的
		if(userLevel>=0 && userLevel<4){
			//如果有选择具体哪个镇
			if(isSelectTown){
				isTown=false;
				area=this.findAreaByCode(town.trim());
			}else{
				isTown=true;
				area=this.findAreaByCode("130434000000");
			}
		}
		//如果是镇和村级别
		if(userLevel==4 || userLevel==5){
			isTown=false;
			area=user.getArea();
		}
		
		//如果是查询镇的
		if(isTown){
			baseSql_rowCount="select a.area as name from  area  as  a  where a.fatherId="+area.getId();
			
			sqlQuery="select a.area as areaName,"
					+ "("+baseSql+" and a1.fatherId=a.id ) as 'poorFamilyCount' , "
					+ "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%因病%' ) as 'illnessCount' ,"
					+ "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%因残%' ) as 'deformityCount' ,"
					+ "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%因学%' ) as 'studyCount' ,"
					+ "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%因灾%' ) as 'disasterCount' ,"
					+ "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%缺土地%' ) as 'landCount' ,"
					+ "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%缺水%' ) as 'waterCount' ,"
				    + "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%缺技术%' ) as 'technologyCount' ,"
				    + "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%缺劳力%' ) as 'labourCount' ,"
					+ "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%缺资金' ) as 'capitalCount' ,"
				    + "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%交通条件落后%' ) as 'trafficCount' ,"
				    + "("+baseSql+" and a1.fatherId=a.id and pf1.other like '%自身发展动力不足%' ) as 'developCount' "
					+ "  from  area  as  a  where a.fatherId="+area.getId();
		}else{
			baseSql_rowCount="select a.area as name from  area  as  a  where "+(userLevel==5?"a.id=":"a.fatherId=")+area.getId();
			
			sqlQuery="select a.area as areaName,"
					+ "("+baseSql+" and a1.id=a.id ) as 'poorFamilyCount' , "
					+ "("+baseSql+" and a1.id=a.id and pf1.other like '%因病%' ) as 'illnessCount' ,"
					+ "("+baseSql+" and a1.id=a.id and pf1.other like '%因残%' ) as 'deformityCount' ,"
					+ "("+baseSql+" and a1.id=a.id and pf1.other like '%因学%' ) as 'studyCount' ,"
					+ "("+baseSql+" and a1.id=a.id and pf1.other like '%因灾%' ) as 'disasterCount' ,"
					+ "("+baseSql+" and a1.id=a.id and pf1.other like '%缺土地%' ) as 'landCount' ,"
					+ "("+baseSql+" and a1.id=a.id and pf1.other like '%缺水%' ) as 'waterCount' ,"
				    + "("+baseSql+" and a1.id=a.id and pf1.other like '%缺技术%' ) as 'technologyCount' ,"
				    + "("+baseSql+" and a1.id=a.id and pf1.other like '%缺劳力%' ) as 'labourCount' ,"
					+ "("+baseSql+" and a1.id=a.id and pf1.other like '%缺资金%' ) as 'capitalCount' ,"
				    + "("+baseSql+" and a1.id=a.id and pf1.other like '%交通条件落后%' ) as 'trafficCount' ,"
				    + "("+baseSql+" and a1.id=a.id and pf1.other like '%自身发展力不足%' ) as 'developCount' "
					+ "  from  area  as  a  where "+(userLevel==5?"a.id=":"a.fatherId=")+area.getId();
		}
		
		if(ispager){
			
		   sqlQuery=sqlQuery+" limit "+(pageNo-1)*pageSize+","+pageSize;	
		}
		
		Session session=this.getCurrentSession();
		SQLQuery query=session.createSQLQuery(sqlQuery);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> result=query.list();
		
		DecimalFormat df = new DecimalFormat("0.00");
		String [] keys=new String[]{"illnessCount","deformityCount","studyCount","disasterCount","landCount"
				,"waterCount","technologyCount","labourCount","capitalCount","trafficCount","developCount"};
		
		String [] name=new String[]{"illnessRatio","deformityRatio","studyRatio","disasterRatio","landRatio"
				,"waterRatio","technologyRatio","labourRatio","capitalRatio","trafficRatio","developRatio"};
		
		for(Map<String,Object> map:result){
			
			int poorFamilyCount=Integer.valueOf(String.valueOf(map.get("poorFamilyCount")));
			
			for(int x=0;x<keys.length;x++){
				int value=Integer.valueOf(String.valueOf(map.get(keys[x])));
				map.put(name[x],poorFamilyCount==0 || value==0 ? "0%" : String.format("%.2f", (new Double(value) / poorFamilyCount)*100)+"%");	
			}
		}
		
		if(ispager){
			List list=session.createSQLQuery(baseSql_rowCount).list();
			return new Pager<>(pageSize, pageNo, list.size(), result);
		}
		return result;
	}
	
	
	
	
	/**
	 * 贫困学生查询 
	 */
	@Override
	@SuppressWarnings("all")
	public Pager queryPoorStudent(final String year, String country, Family family, Integer pageNo, Integer pageSize, McUser user) {
		
//		Long start = System.currentTimeMillis();
		Area userArea = user.getArea();
		UserLevel level = user.getUserLevel();
		String areaCode = "";
		//当前用户信息错误
		if(level.getLevel() > 3 && userArea == null) {
			return new Pager(pageSize,pageNo,0,null);
		}
		if(userArea != null) {
			areaCode = userArea.getAreaCode();
			areaCode = StringUtils.isBlank(areaCode) ? "none" : areaCode;
		}
		final Map<String,String> condition = new HashMap<>();
		
		
		Collections.fill(condition,
			new String[]{"country","village","countryCondition","villageCondition","annual","name","idNumber","schoolName", "institution","isStudent", "enterTime"},
			"%");
		
		//乡镇级
		if(user.getUserLevel().getLevel() == 4) {
			condition.put("country", areaCode.length() > 8 ? areaCode.trim().substring(0,9) + "%" : "none");
		}
		//村级
		if(user.getUserLevel().getLevel() == 5) {
			condition.put("village", areaCode);
		}
	
		if(user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
			condition.put("countryCondition",  country.length() > 8 ? country.trim().substring(0,9)+"%" : "none");
		}
		if(StringUtils.isNotBlank(year)) {
			condition.put("annual", year.trim());
		}
		if(family != null) {
			if(user.getUserLevel().getLevel() < 5 && StringUtils.isNotBlank(family.getVillage())) {
				condition.put("villageCondition", family.getVillage().trim());
			}
			if(StringUtils.isNotBlank(family.getName())) {
				condition.put("name", "%"+family.getName().trim()+"%");
			}
			if(StringUtils.isNotBlank(family.getIdNumber())) {
				condition.put("idNumber", "%"+family.getIdNumber().trim()+"%");
			}
			if(StringUtils.isNotBlank(family.getSchoolName())) {
				condition.put("schoolName", "%"+family.getSchoolName().trim()+"%");
			}
			if(StringUtils.isNotBlank(family.getInstitution())) {
				if(!"全部".equals(family.getInstitution().trim())) {
					condition.put("institution", "%"+family.getInstitution().trim()+"%");
				}
			}
			if(StringUtils.isNotBlank(family.getIsStudent())) {
				if(!"全部".equals(family.getIsStudent().trim())) {
					condition.put("isStudent", "%"+family.getIsStudent().trim()+"%");
				}
			}
			if(StringUtils.isNotBlank(family.getEnterTime())) {
				condition.put("enterTime", "%"+family.getEnterTime().trim()+"%");
			}
		}
		
		getCurrentSession().doWork(new Work() {
			@Override
			public void execute(Connection connection) throws SQLException {
				connection.prepareStatement("set @@autocommit=1").execute();
				PreparedStatement ps = connection.prepareStatement("create temporary table if not exists poor_student as ("
					+ " select f.id as id,f.name as name,f.fid as fid,f.ID_NUMBER as idNumber,f.school as school ,f.SCHOOLNAME as schoolName,f.ENTERTIME as enterTime,f.MAJOR as major,f.INSTITUTION as institution,f.IS_STUDENT as isStudent,f.DO_TYPE as doType,"
					+ " pf.country as country,pf.village as village,a.areaCode as areaCode from family as f,poor_family as pf, area as a "
					+ " where f.IS_DEL='0' and pf.is_del='0' and f.fid=pf.id and f.annual=pf.annual and pf.area_id=a.id and f.annual=? and f.school != '非在校生' and ifnull(f.school,'') != '')");
				ps.setString(1,(StringUtils.isNotBlank(year) ? year :String.valueOf((Calendar.getInstance().get(Calendar.YEAR)))));
				ps.execute();
			}
		});
		
		String fragment = " where t1.areaCode like :country and t1.areaCode like :village and t1.areaCode like :countryCondition and t1.areaCode like :villageCondition "
			+ " and ifnull(t1.name,'') like :name and ifnull(t1.idNumber,'') like :idNumber and ifnull(t1.schoolName,'') like :schoolName and ifnull(t1.institution,'') like :institution  "
			+ " and ifnull(t1.isStudent,'') like :isStudent and ifnull(t1.enterTime,'') like :enterTime  order by t1.village";
		
		StringBuilder hql = new StringBuilder("select "
			+ " t1.id as id, t1.fid as fid,t1.country as country,t1.village as village,"
			+ " t1.name as name, t1.idNumber as idNumber, t1.school as school, t1.schoolName as schoolName, "
			+ " t1.enterTime as enterTime, t1.major as major, t1.institution as institution, "
			+ " t1.isStudent as isStudent, t1.doType as doType from poor_student as t1 " + fragment);
		
		StringBuilder count = new StringBuilder("select count(1) from poor_student as t1 " + fragment );
		
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		int pageStart = (pageNo - 1) * pageSize;
		
		List<Map> list = getCurrentSession().createSQLQuery(hql.toString())
		.setFirstResult(pageStart).setMaxResults(pageSize)
		.setProperties(condition)
		.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
		.list();
		BigInteger rows = (BigInteger) getCurrentSession().createSQLQuery(count.toString()).setProperties(condition).uniqueResult();
		
		Pager pager = new Pager(pageSize,pageNo,rows.intValue(),list);
		
		//户主查询
		List<Map<String,Object>> householderList = new ArrayList<>(0);
		if(pageSize > 1000) {
			householderList =  getCurrentSession()
				.createSQLQuery("select f.`NAME` as householder,f.id_number as householderIdNumber,f.fid as fid from family as f where annual=? and f.IS_DEL='0' and f.relation='户主'")
				.setString(0, StringUtils.isNotBlank(year) ? year : String.valueOf(Calendar.getInstance().get(Calendar.YEAR))).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		} else {
			List ids = new ArrayList<>(list.size());
			for (Map e: list) {
				ids.add(e.get("fid"));
			}
			if(!ids.isEmpty()) {
				householderList =  getCurrentSession()
					.createSQLQuery("select f.`NAME` as householder,f.id_number as householderIdNumber,f.fid as fid from family as f where annual=? and f.IS_DEL='0' and f.relation='户主' and f.fid in( "
						+Arrays.toString(ids.toArray()).replaceAll("[\\[\\]]", "")+")")
					.setString(0, StringUtils.isNotBlank(year) ? year : String.valueOf(Calendar.getInstance().get(Calendar.YEAR))).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			}
		}
		Map<Object,String> householderMap = new HashMap<>(householderList.size(),1);
		Map<Object,String> householderIdNumberMap = new HashMap<>(householderList.size(),1);
		for(Map e : householderList) {
			householderMap.put(e.get("fid"),String.valueOf(e.get("householder")));
			householderIdNumberMap.put(e.get("fid"),String.valueOf(e.get("householderIdNumber")));
		}
		for (Map<String, Object> map : list) {
			map.put("householder", householderMap.get(map.get("fid")));
			map.put("householderIdNumber", householderIdNumberMap.get(map.get("fid")));
		}
		dropTempTable("poor_student");
		
		return pager;
	}

	/**
	 * 户主查询 
	 */
	@Override
	public List<Family> findHouseholder(Integer... ids) {
		if(ids != null && ids.length != 0 ) {
			String hql = "from Family as f where f.isDel=0 and f.relation = '户主' and f.fid in( "
					+Arrays.toString(ids).replaceAll("[\\[\\]]", "")+")";
			return find(hql.toString());
		}
		return new ArrayList<>();
	}


	/** (non-Javadoc)
	 * 户主异常 
	 */
	@Override
	@SuppressWarnings("all")
	public Pager queryExceptHouseholder(final String year, String country, String village, Integer type, Integer pageNo, Integer pageSize,
		McUser user) {
		
		//查询类型
		type = type == null || type<1 || type > 3 ? 3 : type;
		final Integer finalType = type;
		//是否所有类型
		boolean allCase = type == 3;
		
		Long start = System.currentTimeMillis();
		Area userArea = user.getArea();
		UserLevel level = user.getUserLevel();
		String areaCode = "";
		int pageStart = (pageNo - 1) * pageSize;
		//当前用户信息错误
		if(level.getLevel() > 3 && userArea == null) {
			return new Pager(pageSize,pageNo,0,null);
		}
		if(userArea != null) {
			areaCode = userArea.getAreaCode();
			areaCode = StringUtils.isBlank(areaCode) ? "none" : areaCode;
		}
		
		getCurrentSession().doWork(new Work() {
			@Override
			public void execute(Connection connection) throws SQLException {
				connection.prepareStatement("set @@autocommit=1").execute();
				//无户主
				if(finalType ==1 || finalType==3) {
					PreparedStatement ps = 
						connection.prepareStatement("create temporary table if not exists no_householder as ("
							+ " select t1.fid as fid from (select id as fid from poor_family as pf where pf.is_del = '0' and pf.annual=?) as t1 "
							+ " LEFT JOIN (select f.fid as fid from family as f  where f.is_del='0' and f.annual=? and f.relation='户主') as t2 "
							+ " on t1.fid = t2.fid where t2.fid is null)");
					
					ps.setString(1,StringUtils.isNotBlank(year) ? year : String.valueOf(Calendar.getInstance().get(Calendar.YEAR)));
					ps.setString(2,StringUtils.isNotBlank(year) ? year : String.valueOf(Calendar.getInstance().get(Calendar.YEAR)));
					ps.execute();
				}
				
				//多户主
				if(finalType == 2 || finalType==3) {
					PreparedStatement ps = 
						connection.prepareStatement("create temporary table if not exists multi_householder as "
							+ "(select fid from family where relation='户主' and is_del='0' and annual="
							+(StringUtils.isNotBlank(year) ? year : Calendar.getInstance().get(Calendar.YEAR))+" group by fid having(count(fid)>1))");
					ps.execute();
				}
			}
		});
		String base = "select f.id as id, p.id as pid, if(p.migrate='1','是',if(p.migrate is null,'','否')) as migrate ,p.number as familyNo, f.number as personNo, f.name as name, f.sex as sex, f.id_number as idNumber, "
			+ " f.relation as relation,f.nation as nation "
			+ " from poor_family as p, family as f,area as a, ";
		String fragment= "  where p.id = f.fid and f.fid=t1.fid  and f.is_del='0' and p.is_del='0' and a.id=p.area_id and p.annual=f.annual and a.areaCode like :country and a.areaCode like :village and a.areaCode like :countryCondition and a.areaCode like :villageCondition";
		String conditionStr = " and a.id=p.area_id and p.annual=f.annual and p.annual like :annual and a.areaCode like :country and a.areaCode like :village and a.areaCode like :countryCondition and a.areaCode like :villageCondition ";
		//无户主
		StringBuilder noH = new StringBuilder(base + "no_householder as t1 " + fragment);
		StringBuilder noHRows = new StringBuilder(" select count(1) from poor_family as p, family as f,area as a, no_householder as t1 " + fragment);
		StringBuilder noHCount = new StringBuilder("select p.id as pid, count(1) as count from poor_family as p,family as f,area as a,no_householder as t1 "
				+ "where p.id = f.fid and f.fid=t1.fid and f.is_del='0' and p.is_del='0' "+ conditionStr);
		
		//多户主
		StringBuilder multiH = new StringBuilder(base + "multi_householder as t1 " + fragment);
		StringBuilder multiHRows = new StringBuilder(" select count(1) from poor_family as p, family as f,area as a, multi_householder as t1 " + fragment);
		StringBuilder multiHCount = new StringBuilder("select p.id as pid, count(1) as count from poor_family as p,family as f,area as a,multi_householder as t1 "
				+ "where p.id = f.fid and f.is_del='0' and p.is_del='0' and  f.fid=t1.fid "+ conditionStr);
		
		Map condition = Collections.fill(new HashMap<>(),new String[]{"country","village","annual","countryCondition","villageCondition"},"%");
		
		//乡镇级
		if(user.getUserLevel().getLevel() == 4) {
			condition.put("country", areaCode.trim().length() > 8 ? areaCode.trim().substring(0,9)+"%" : "none");
		}
		//村级
		if(user.getUserLevel().getLevel() == 5) {
			condition.put("village", areaCode);
		}
		
		if(StringUtils.isNotBlank(year)) {
			condition.put("annual", year.trim());
		}
		if(user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country) && StringUtils.isBlank(village)) {
			condition.put("countryCondition", country.length() > 8 ? country.trim().substring(0,9)+"%" : "none");
		}
		if(user.getUserLevel().getLevel() < 5 && StringUtils.isNotBlank(village)) {
			condition.put("villageCondition", village.trim());
		}
		
		noH.append(" order by familyNo asc,personNo asc ");
		noHCount.append(" group by p.id ");
		multiH.append(" order by familyNo asc,personNo asc ");
		multiHCount.append("  group by p.id");
		
		//无户主
		List noHList = new ArrayList<>(0);
		Integer nRows = 0;
		if(type ==1 || type==3) {
			//无户主总记录数
			nRows = ((BigInteger)getCurrentSession().createSQLQuery(noHRows.toString()).setProperties(condition).uniqueResult()).intValue();
		}
		//多户主
		List multiHList = new ArrayList<>(0);
		Integer mRows = 0;
		if(type == 2 || type== 3) {
			//多户主总记录数
			mRows =((BigInteger)getCurrentSession().createSQLQuery(multiHRows.toString()).setProperties(condition).uniqueResult()).intValue();
		}
		switch(type) {
			//所有情况
			case 3:
			//无户主
		case 1:
			if(pageNo <= (nRows + (pageSize-1)) / pageSize) {
				noHList = getCurrentSession().createSQLQuery(noH.toString()).setFirstResult(pageStart).setMaxResults(pageSize)
					.setProperties(condition).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
				// noHList = findPageObjects(noH.toString(),pageNo,
				// noHPageSize,condition);
				List noHListCount = getCurrentSession().createSQLQuery(noHCount.toString()).setProperties(condition)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
				for (Map map : (List<Map>) noHListCount) {
					for (Map o : (List<Map>) noHList) {
						if (((BigInteger) map.get("pid")).equals(o.get("pid"))) {
							o.put("count", map.get("count"));
							o.put("exceptType", "无户主");
						}
					}
				}
			}
			if (!allCase) {
				dropTempTable("no_householder");
				return new Pager(pageSize, pageNo, nRows, noHList);
			}
			// 多户主
		case 2:
			int tempPageSize = allCase ? pageSize - noHList.size() > 0 ? pageSize - noHList.size() : 0 : pageSize;
			int tempPageNo = pageNo - (nRows + (pageSize-1)) / pageSize ;
			tempPageNo = tempPageNo < 1 ? 1 : tempPageNo;
			if(tempPageSize == 0) {
				multiHList = new ArrayList<>(0);
			} else {
				pageStart = allCase ? noHList.isEmpty() && nRows % pageSize != 0 ? (tempPageNo-1)*tempPageSize + (pageSize - nRows % pageSize) : (tempPageNo-1)*tempPageSize : (tempPageNo-1)*tempPageSize;
				multiHList = getCurrentSession()
					.createSQLQuery(multiH.toString())
					.setFirstResult(pageStart)
					.setMaxResults(tempPageSize)
					.setProperties(condition).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			}
			List multiHListCount = getCurrentSession().createSQLQuery(multiHCount.toString()).setProperties(condition)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			for (Map map : (List<Map>) multiHListCount) {
				for (Map o : (List<Map>) multiHList) {
					if (((BigInteger) map.get("pid")).equals(o.get("pid"))) {
						o.put("count", map.get("count"));
						o.put("exceptType", "多户主");
					}
				}
			}
			if (!allCase) {
				dropTempTable("multi_householder");
				return new Pager(pageSize, pageNo, mRows, multiHList);
			}
			//所有情况
			if(multiHList != null) {
				if(noHList != null) {
					noHList.addAll(multiHList);
				} else {
					noHList = new ArrayList<>();
					noHList.addAll(multiHList);
				}
			}
			dropTempTable("no_householder");
			dropTempTable("multi_householder");
			return new Pager(pageSize,pageNo,allCase ? mRows+nRows : type == 1 ? nRows : mRows,noHList);
		}
		
		dropTempTable("no_householder");
		dropTempTable("multi_householder");
		return new Pager(pageSize,pageNo,0,new ArrayList<>(0));
	}
	
	/**
	 * 删除临时表
	 * @param table
	 */
	private void dropTempTable(final String table){
		getCurrentSession().doWork(new Work() {
			@Override
			public void execute(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement("drop table "+ table);
				ps.execute();
			}
		});
	}


	/**
	 * 证件号重复
	 */
	@Override
	@SuppressWarnings("all")
	public Pager<?> queryDuplicateID(Integer pageNo, Integer pageSize, final String year, String country, McUser user) {

//		Long start = System.currentTimeMillis();
		Area userArea = user.getArea();
		UserLevel level = user.getUserLevel();
		String areaCode = "";
		//当前用户信息错误
		if(level.getLevel() > 3 && userArea == null) {
			return new Pager(pageSize,pageNo,0,null);
		}
		if(userArea != null) {
			areaCode = userArea.getAreaCode();
			areaCode = StringUtils.isBlank(areaCode) ? "none" : areaCode;
		}
		Map condition = new HashMap<>();
		
		getCurrentSession().doWork(new Work() {
			@Override
			public void execute(Connection connection) throws SQLException {
				connection.prepareStatement("set @@autocommit=1").execute();
				PreparedStatement ps = connection.prepareStatement("create temporary table if not exists duplicate_id_number as "
					+ "(select id_number,annual from family as f where f.is_del='0' and fid is not null and annual=? group by annual,f.id_number having count(id_number) > 1) ");
				ps.setString(1, StringUtils.isNotBlank(year) ? year : ApplicationProperties.getAnnual());
				ps.execute();
			}
		});
		
		StringBuilder hql = new StringBuilder("select t2.*,pv.GROUP_NUMBER as groupNumber "
			 + " from poor_village as pv RIGHT JOIN "
			 + " (select f.id as id, f.annual as annual ,f.name as name, f.sex as sex, f.id_number as idNumber, f.relation as relation, f.nation as nation,"
			 + " f.fid as fid,f.telephone as telephone, p.perovince as province, p.city as city, p.county as county,p.country as country, "
			 + " p.village as village,p.poverty as poverty,p.attribute as attribute,a.id as areaId, a.area as area,a.areaCode as areaCode, p.id as pid "
			 + " from family as f, poor_family as p ,area as a , duplicate_id_number as t1 "
			 + " where p.id = f.fid and f.is_del='0' and p.is_del='0' and a.id=p.area_id and f.annual = p.annual and t1.annual=f.annual and f.id_number = t1.id_number) as t2 "
			 + " on pv.AREA_ID=t2.areaId and pv.ANNUAL=t2.annual and pv.is_del='0' "
			 + " where t2.annual like :annual and t2.areaCode like :country and t2.areaCode like :village and t2.areaCode like :areaCondition order by t2.idNumber ");
		
		StringBuilder count = new StringBuilder("select count(1)"
			 + " from poor_village as pv RIGHT JOIN "
			 + " (select f.annual as annual,a.id as areaId,  a.areaCode as areaCode"
			 + " from family as f, poor_family as p ,area as a,duplicate_id_number as t1 "
			 + " where p.id = f.fid and f.is_del='0' and p.is_del='0' and a.id=p.area_id and f.annual = p.annual and t1.annual=f.annual and f.id_number = t1.id_number) as t2 "
			 + " on pv.AREA_ID=t2.areaId and pv.ANNUAL=t2.annual and pv.is_del='0' "
			 + " where t2.annual like :annual and t2.areaCode like :country and t2.areaCode like :village and t2.areaCode like :areaCondition");
		
		condition.put("country", "%");
		condition.put("village","%");
		condition.put("annual","%");
		condition.put("areaCondition","%");
		
		//乡镇级
		if(user.getUserLevel().getLevel() == 4) {
			condition.put("country", areaCode.length() > 8 ? areaCode.trim().substring(0,9)+"%" : "none");
		}
		//村级
		if(user.getUserLevel().getLevel() == 5) {
			condition.put("village",areaCode);
		}
		
		//年度（添加时间）
		if(StringUtils.isNotBlank(year)) {
			condition.put("annual",year.trim());
		}
		//镇
		if(user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country) ) {
			condition.put("areaCondition",country.length() > 8 ? country.trim().substring(0,9)+"%" : "none");
		}

		Query query = getCurrentSession().createSQLQuery(hql.toString());
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		int pageStart = (pageNo - 1) * pageSize;
		query.setProperties(condition);
		query.setFirstResult(pageStart);
		query.setMaxResults(pageSize);
		List<Map> result = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		
		query = getCurrentSession().createSQLQuery(count.toString());
		query.setProperties(condition);
		BigInteger rows = (BigInteger) query.uniqueResult();
		Pager pager = new Pager<Map>(pageSize, pageNo,rows.intValue(), result);
		
		//户主查询
		List<Map<String,Object>> householderList = new ArrayList<>(0);
		if(pageSize > 1000) {
			householderList =  getCurrentSession()
				.createSQLQuery("select f.`NAME` as householder,f.id_number as householderIdNumber,f.fid as fid from family as f where annual=? and f.IS_DEL='0' and f.relation='户主'")
				.setString(0, StringUtils.isNotBlank(year) ? year : ApplicationProperties.getAnnual()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		} else {
			List ids = new ArrayList<>(result.size());
			for (Map e: result) {
				ids.add(e.get("fid"));
			}
			if(!ids.isEmpty()) {
				householderList =  getCurrentSession()
					.createSQLQuery("select f.`NAME` as householder,f.id_number as householderIdNumber,f.fid as fid from family as f where annual=? and f.IS_DEL='0' and f.relation='户主' and f.fid in( "
						+Arrays.toString(ids.toArray()).replaceAll("[\\[\\]]", "")+")")
					.setString(0, StringUtils.isNotBlank(year) ? year : ApplicationProperties.getAnnual()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			} 
		}
		Map<Object,String> householderMap = new HashMap<>(householderList.size(),1);
		for(Map e : householderList) {
			householderMap.put(e.get("fid"),String.valueOf(e.get("householder")));
		}
		for (Map<String, Object> map : result) {
			map.put("householder", householderMap.get(map.get("fid")));
		}
		
		dropTempTable("duplicate_id_number");
		return pager;
	}


	/**
	 * 贫困户人口规模统计
	 */
	@Override
	@SuppressWarnings("all")
	public Pager populationStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String poverty) {
		
		boolean isVillage = false;
		//获取区域（镇/村）
		//未设置镇/村条件则默认为县，以镇为单位
		//如果设置镇,统计该镇所有村
		String getArea = "";
		if(user.getUserLevel().getLevel() < 4) {
			getArea = "select new Map(a1.areaCode as areaCode, a1.area as area, a1.id as id) from Area as a1 "
				+ " where a1.fatherId = (select a.id from Area as a where a.areaCode = '130434000000') order by a1.areaCode";
			//
			if(StringUtils.isNotBlank(country)) {
				getArea  = "select new Map(a1.areaCode as areaCode, a1.area as area, a1.id as id) from Area as a1 "
					+ " where a1.fatherId!=3 and  a1.areaCode like '" + (country.length() > 8 ? country.substring(0,9) + "%" : "none") + "' order by a1.areaCode";
				isVillage = true;
			}
			if(StringUtils.isNotBlank(village)) {
				getArea = "select new Map(a1.areaCode as areaCode, a1.area as area, a1.id as id) from Area as a1 where a1.areaCode ='"+village.trim()+"'";
				isVillage = true;
			}
		}
		
		//镇级权限
		if(user.getUserLevel().getLevel() == 4) {
			Area area = user.getArea();
			if(area == null) {
				return new Pager(pageSize,pageNo,0,new ArrayList<>(0));
			}
			//只能查看该镇的村
			getArea = "select new Map(a1.areaCode as areaCode, a1.area as area, a1.id as id) from Area as a1 "
				+ " where a1.fatherId!=3 and a1.areaCode like '"+ (area.getAreaCode().length() > 8 ? area.getAreaCode().substring(0, 9) : "none")+"%'"
				+ (StringUtils.isNotBlank(village) ? " and a1.areaCode = '" + village.trim()+"'" : " ")
				+ (StringUtils.isNotBlank(country) ? " and a1.areaCode like '"+ (country.length()>8 ? country.substring(0,9) + "%" : "none") :" ") + "'"
				+ " order by a1.areaCode";
			isVillage = true;
		}
		
		//村级权限限定
		if (user.getUserLevel().getLevel() == 5) {
			Area area = user.getArea();
			if(area != null ) {
				// 重置地区
				getArea = "select new Map(a1.areaCode as areaCode, a1.area as area, a1.id as id) from Area as a1 where a1.areaCode = "+ area.getAreaCode();
			} else {
				return new Pager(pageSize,pageNo,0,new ArrayList<>(0));
			}
			isVillage = true;
		}

		List result = new ArrayList<>();
		String area = "";
		String areaCode = "";
		Map map = null;
		Map temp = null;
		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);
		for(Map e : (List<Map>)findPageObjects(getArea,pageNo,pageSize,new HashMap<>())) {
			areaCode = e.get("areaCode") != null ? e.get("areaCode").toString().trim() : "none";
			
			String sql = "SELECT a.area as area, f.FID as fid,count(1) as population FROM `family` as f, poor_family as pf, area as a "
				+ " WHERE f.IS_DEL='0' AND pf.IS_DEL='0' AND f.ANNUAL=pf.ANNUAL AND pf.ATTRIBUTE!='一般农户' AND f.FID = pf.ID AND pf.AREA_ID = a.ID AND a.areaCode like '"
				+ (isVillage ? areaCode : areaCode.length() > 8 ? areaCode.substring(0,9)+"%" : "none")
				+ "' " + (StringUtils.isNotBlank(year) ? " and pf.annual='" + year.trim() + "'" : " ")
				+ (StringUtils.isNotBlank(poverty) ? " and pf.POVERTY='" + poverty.trim()+"' " : " ")
				+ " AND f.FID is not null group by FID ";
			
			//当前地区贫困户总数
			//总转移性收入
			Map total= (Map) find("select new Map( count(1) as totalPoorFamily ,sum(pf.transfer) as transfer) from PoorFamily as pf "
				+ " where pf.isDel='0' and pf.area.areaCode like '"
				+ (isVillage ? areaCode : areaCode.length() > 8 ? areaCode.substring(0,9)+"%" : "none") +"' "
				+ (StringUtils.isNotBlank(poverty) ? " and pf.poverty='" + poverty.trim()+"' " : " ")
				+ (StringUtils.isNotBlank(year) ? " and pf.annual='" + year.trim() +"'" : " ")).get(0);
			Long totalPoorFamily = (Long) total.get("totalPoorFamily");
			Query query = getCurrentSession().createSQLQuery(sql);
			List<Map> list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			
			temp = new HashMap<>();
			temp.put("oneCount",0);
			temp.put("onePercent","0%" );
			temp.put("twoCount",0);
			temp.put("twoPercent","0%");
			temp.put("threeCount",0);
			temp.put("threePercent","0%");
			temp.put("fourCount",0 );
			temp.put("fourPercent","0%");
			temp.put("fiveCount",0);
			temp.put("fivePercent","0%");
			temp.put("sixCount",0);
			temp.put("sixPercent","0%");
			temp.put("noneCount",0);
	    	temp.put("nonePercent","0%");
			for(Map r : (List<Map>)list) {
				switch(((BigInteger)r.get("population")).intValue()) {
					case 1 :
						temp.put("oneCount",(Integer)temp.get("oneCount") + 1  );
						temp.put("onePercent",formatter.format((Integer)temp.get("oneCount")/ Double.valueOf(totalPoorFamily)) );
						break;
					case 2 :
						temp.put("twoCount",(Integer)temp.get("twoCount") + 1   );
						temp.put("twoPercent",formatter.format((Integer)temp.get("twoCount")/ Double.valueOf(totalPoorFamily)) );
						break;
					case 3 :
						temp.put("threeCount",(Integer)temp.get("threeCount") + 1  );
						temp.put("threePercent",formatter.format((Integer) temp.get("threeCount")/ Double.valueOf(totalPoorFamily)) );
						break;
					case 4 :
						temp.put("fourCount",(Integer)temp.get("fourCount") + 1   );
						temp.put("fourPercent",formatter.format((Integer) temp.get("fourCount")/ Double.valueOf(totalPoorFamily)) );
						break;
					case 5 :
						temp.put("fiveCount",(Integer)temp.get("fiveCount") + 1   );
						temp.put("fivePercent",formatter.format((Integer) temp.get("fiveCount")/ Double.valueOf(totalPoorFamily)) );
						break;
					default :
						temp.put("sixCount", (Integer)temp.get("sixCount") + 1   );
						temp.put("sixPercent",formatter.format((Integer)temp.get("sixCount")/ Double.valueOf(totalPoorFamily)) );
				}
			}
			//空户
			if(list.size() < totalPoorFamily) {
		    	temp.put("noneCount",totalPoorFamily - list.size() );
		    	temp.put("nonePercent",formatter.format((Long)temp.get("noneCount")/ Double.valueOf(totalPoorFamily)) );
			}

			map = new HashMap<>();
			map.put("totalPoorFamily",totalPoorFamily);
			map.put("area",e.get("area"));
			map.put("transferAVG",total.get("transfer") == null ? 0 : String.format("%.2f",new Double((String)total.get("transfer"))/totalPoorFamily));
			map.put("verbose", temp);
			result.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(getArea,null).size(),result);
	}


	/**
	 * 贫困户党员情况统计 
	 */
	@Override
	@SuppressWarnings("all")
	public Pager CPCStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String poverty) {
		//当前登录用户所在区域
		Area userArea = user.getArea();
		//等级
		Integer level = user.getUserLevel().getLevel();
		
		List<Map> areaList = new ArrayList<>(0);
		Integer rowCount = 0;
		
		//镇以上权限
		if(level < LevelUtil.TOWN) {
			Map map = getDistrict(pageNo,pageSize,StringUtils.isNotBlank(village) 
				? village 
				: StringUtils.isBlank(country) ? "" : country.length() > 8 ? country.substring(0,9)+"%" :"none");
			areaList = (List<Map>) map.get("list");
			rowCount = (Integer) map.get("rows");
		}

		//镇级权限
		if(level == LevelUtil.TOWN) {
			if(userArea == null) {
				return new Pager(pageSize,pageNo,0,null);
			}
			String userCountry= userArea.getAreaCode().length() > 8 ? userArea.getAreaCode().substring(0,9) + "%" : "none";
			if(StringUtils.isBlank(country) ) {
				Map map = getDistrict(pageNo, pageSize, userCountry);
				areaList = (List<Map>) map.get("list");
				rowCount = (Integer) map.get("rows");
			} else {
				country = StringUtils.isNotBlank(country) && country.length() > 8 ? country.substring(0,9) + "%" : "%";
				Map map = getDistrict(pageNo, pageSize, userCountry, userCountry,country,StringUtils.isBlank(village) ? "%" : village);
				areaList = (List<Map>) map.get("list");
				rowCount = (Integer) map.get("rows");
			}
		}
		
		//村级权限
		if(level == LevelUtil.VILLAGE) {
			if(userArea == null) {
				return new Pager(pageSize,pageNo,0,null);
			}
			String userCountry= userArea.getAreaCode();
			country = StringUtils.isNotBlank(country) && country.length() > 8 ? country.substring(0,9) + "%" : "%";
			Map map = getDistrict(pageNo, pageSize, null, userCountry,country,village == null ? "%" : village);
			areaList = (List<Map>) map.get("list");
			rowCount = (Integer) map.get("rows");
		}

		boolean isCountry = !areaList.isEmpty() ? areaList.get(0).get("areaCode").toString().matches(".+000") ? true : false : false;
		String areaCode = "";
		
		Long totalPoorFamily = 0L;
		Long totalPoor = 0L;
		Map verbose = new HashMap<>();
		List result = new ArrayList<>();
		for(Map area : areaList) {
			Integer totalCPC = 0;
			Integer totalPreCPC = 0;
			verbose = new HashMap<>();
			areaCode = area.get("areaCode").toString();
			
			totalPoorFamily = (Long) find("select count(1) from PoorFamily as pf where pf.isDel=0 and pf.area.areaCode like '"
				+ (isCountry ? areaCode.length() > 8 ? areaCode.substring(0, 9)+"%" : "none" : areaCode)  +"'"
				+ (StringUtils.isNotBlank(year) ? " and pf.annual='" + year.trim() + "'" : "") 
				+ (StringUtils.isNotBlank(poverty) ? " and pf.poverty like '%"+ poverty +"%'" : "")).get(0);
			totalPoor = (Long)find("select count(1) from PoorFamily as pf, Family as f where pf.id=f.fid and pf.isDel='0' and f.isDel='0' and f.annual=pf.annual and pf.area.areaCode like '"
				+ (isCountry ? areaCode.length() > 8 ? areaCode.substring(0, 9)+"%" : "none" : areaCode)  +"'"
				+ (StringUtils.isNotBlank(year) ? " and pf.annual='" + year.trim() + "'" : "")
				+ (StringUtils.isNotBlank(poverty) ? " and pf.poverty like '%"+ poverty +"%'" : "")).get(0);
			
			String base = "select new Map(pf.id as fid,f.political as political, count(pf.id) as count)"
				+ " from Family as f, PoorFamily as pf "
				+ " where f.isDel='0' and pf.isDel='0' and f.annual=pf.annual and f.fid=pf.id ? "
				+ (StringUtils.isNotBlank(year) ? " and pf.annual='" + year.trim() + "'" : "")
				+ (StringUtils.isNotBlank(poverty) ? " and pf.poverty like '%"+ poverty +"%'" : "")
				+ " and pf.area.areaCode like '"+ (isCountry ? areaCode.length() > 8 ? areaCode.substring(0, 9)+"%" : "none" : areaCode)  +"'"
				+ " group by pf.id ";
			//党员
			String CPC = " and f.political like '%中共党员%' ";
			//预备党员
			String preCPC = " and (f.political like '%预备中共党员%' or f.political like '%中共预备党员%') ";
			
			List<Map> CPCList = find(base.replaceAll("\\?", CPC));
			List<Map> preCPCList = find(base.replaceAll("\\?", preCPC));
			
			for(Map e : CPCList) {
				totalCPC += ((Long)e.get("count")).intValue();
			}
			for(Map e : preCPCList) {
				totalPreCPC += ((Long)e.get("count")).intValue();
			}
			
			verbose.put("area", area.get("area"));
			verbose.put("totalPoorFamily", totalPoorFamily);
			verbose.put("totalPoor", totalPoor);
			verbose.put("countCPCP", CPCList.size());
			verbose.put("percentCPCP", totalPoorFamily == 0 || CPCList.size() == 0 ? "0%" :String.format("%.2f",(Integer)verbose.get("countCPCP")/new Double(totalPoorFamily) * 100) + "%");
			verbose.put("countPreCPCP",preCPCList.size());
			verbose.put("percentPreCPCP",totalPoorFamily == 0 || preCPCList.size() == 0 ? "0%" : String.format("%.2f",(Integer)verbose.get("countPreCPCP")/new Double(totalPoorFamily) * 100) + "%");
			verbose.put("totalCPCH",totalCPC);
			verbose.put("totalPreCPCH",totalPreCPC);
			verbose.put("percentCPCH",totalCPC == 0 || totalCPC == 0 ? "0%" :String.format("%.2f",totalCPC/new Double(totalPoor) * 100) + "%");
			verbose.put("percentPreCPCH",totalPreCPC == 0 || totalPreCPC == 0 ? "0%" :String.format("%.2f",totalPreCPC/new Double(totalPoor) * 100) + "%");
			result.add(verbose);
		}
		return new Pager(pageSize,pageNo,rowCount,result);
	}
	
	/**
	 * 区域获取
	 * @return
	 */
	@SuppressWarnings("all")
	private Map getDistrict(Integer pageNo,Integer pageSize,String areaCodeCondition,String... areaCodes) {
		//获取所有镇
		if(StringUtils.isBlank(areaCodeCondition) && (areaCodes == null || areaCodes.length==0) ) {
			Map result = new HashMap<>();
			String hql = "select new Map(a1.id as id,  a1.fatherId as fatherId ,a1.area as area, a1.areaCode as areaCode) from Area as a1 "
				+ " where a1.fatherId = (select a.id from Area as a where a.areaCode = '130434000000')  order by a1.areaCode";
			List<Map> list = findPageObjects(hql, pageNo, pageSize, new HashMap<>());
			Integer rows = queryList(hql,new HashMap<>()).size();
			result.put("list", list);
			result.put("rows", rows);
			return result;
		}
		
		if(StringUtils.isNotBlank(areaCodeCondition)) {
			//获取指定镇或该镇所有村
			Map condition = new HashMap<>();
			Map result = new HashMap<>();
			condition.put("areaCondition", areaCodeCondition);
			String hql = "select new Map(a.id as id,  a.fatherId as fatherId, a.area as area, a.areaCode as areaCode)"
				+ " from Area as a where a.fatherId != 3 and a.areaCode like :areaCondition order by a.areaCode";
			List<Map> list = findPageObjects(hql,pageNo,pageSize,condition);
			Integer rows = queryList(hql,condition).size();
			result.put("list", list);
			result.put("rows", rows);
			return result;
		}
		
		if(StringUtils.isBlank(areaCodeCondition) && areaCodes != null && areaCodes.length > 0) {
			Map result = new HashMap<>();
			StringBuilder getArea = new StringBuilder("select new Map(a.id as id,  a.fatherId as fatherId, a.area as area, a.areaCode as areaCode)"
				+ " from Area as a where a.fatherId != 3 ");
			for(String areaCode : areaCodes) {
				getArea.append(" and a.areaCode like '" + areaCode + "'");
			}
			List list = findPageObjects(getArea.toString(),pageNo,pageSize,new HashMap<>());
			Integer rows = queryList(getArea.toString(),new HashMap<>()).size();
			result.put("list", list);
			result.put("rows", rows);
			return result;
		}
		return new HashMap<>(0);
	}

	/**
	 * 贫困户帮扶责任人落实情况统计表
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public Pager findFamilyLiable(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
        boolean isCountry = StringUtils.isBlank(country);
        String getDistrict = isCountry
                //获取镇
                ? "select new Map(a1.areaCode as areaCode, a1.area as area) from Area as a1 where a1.fatherId = (select a.id from Area as a where a.areaCode = '130434000000')"
                //获取村
                : " select new Map(a.id as id,areaCode as areaCode,a.area as area) from Area as a where a.fatherId=(select a1.id from Area as a1 where a1.areaCode='"+ country +"')";

        List result = new ArrayList<>();
        String area = "";
        String areaCode = "";

        Map map = null;
        Map temp = null;

        for(Map e : (List<Map>)findPageObjects(getDistrict,pageNo,pageSize,new HashMap<>())) {
            areaCode = e.get("areaCode") != null ? e.get("areaCode").toString() : "none";
            //统计各镇结果
            String sql = "SELECT count(*)  as liable "
                    + " from poor_family as pf, (select distinct poorFamilyId from help_person_and_poor_family) hp "
                    + " where pf.id = hp.poorFamilyId "
                    + " AND pf.IS_DEL= '0' " +(StringUtils.isNotBlank(type) ? " and pf.poverty='"+type+"'" : " ")
                    + (StringUtils.isNotBlank(year) ? " and pf.annual='" + year+"'" : " ")
                    + " and pf.AREA_ID IN (SELECT id from area where areaCode LIKE '"+ (isCountry ? areaCode.substring(0,9)+"%" : areaCode) +"') GROUP BY "+ (StringUtils.isNotBlank(village) ? "  pf.village" : "  pf.country " );
            Query query = getCurrentSession().createSQLQuery(sql);
            List list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
            Object totalPoorVillage = getCurrentSession().createSQLQuery("SELECT COUNT(*) FROM poor_family pfy where pfy.IS_DEL='0' " +(StringUtils.isNotBlank(type) ? " and pfy.poverty='"+type+"'" : " ") + (StringUtils.isNotBlank(year) ? " and pfy.annual='" + year+"'" : " " )
                    + "and pfy.AREA_ID IN (SELECT id from area where areaCode LIKE '"+ (isCountry ? areaCode.substring(0,9)+"%" : areaCode )+ "') GROUP BY "+ (StringUtils.isNotBlank(village) ? "  pfy.village" : "  pfy.country " )).uniqueResult();
            map = new HashMap<>();
            temp = new LinkedHashMap<>();
            temp.put("total", totalPoorVillage == null ? "0" : totalPoorVillage.toString());   //贫困户数
            temp.put("liable","0");  // 落实帮扶责任人户数.户数
            temp.put("liableP", "0%");
            for(Map r : (List<Map>)list) {
                String liableP= "0%";
                String  tl = temp.get("total").toString();
                if (!tl.equals("0")&&StringUtil.isNotBlank(tl)) {
                    String liable = StringUtil.isNotBlank(((Map) r).get("liable").toString())?(String)((Map) r).get("liable").toString():"0";

                    liableP = BigDecimal.valueOf(Long.valueOf(liable)).divide(BigDecimal.valueOf(Long.valueOf(tl)), 4, BigDecimal.ROUND_HALF_UP)
                            .multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
                }
                temp.put("liable", (((Map) r).get("liable"))!=null? ((Map) r).get("liable") : "0");  // 落实帮扶责任人户数.户数
                temp.put("liableP", liableP);  							 // 落实帮扶责任人户数.比例
            }
            map.put("verbose",temp);
            map.put("region", e.get("area")); //地区
            result.add(map);
        }
        return new Pager(pageSize,pageNo,queryList(getDistrict,null).size(),result);
	}


	@Override
	public void batchSave(Object object, int i) {
		getCurrentSession().save(object);
	/*	if (i % 500 == 0) { // 每100个刷新并写入数据库
			getCurrentSession().flush(); // 保持与数据库数据的同步
			getCurrentSession().clear(); // 清除Session级别的一级缓存的全部数据，及时释放占用的内存
		}*/

	}


	@Override
	public void batchModifyByHql(String hql, int i) {
		getCurrentSession().createQuery(hql).executeUpdate();
	/*	if (i % 500 == 0) { // 每100个刷新并写入数据库
			getCurrentSession().flush(); // 保持与数据库数据的同步
			getCurrentSession().clear(); // 清除Session级别的一级缓存的全部数据，及时释放占用的内存
		}*/
		
	}


	@Override
	public Long addPoorFamilyHistofy(PoorFamilyHistory poorFamilyHistory) {
		return (Long) getCurrentSession().save(poorFamilyHistory);
	}


	/**
	 * 获取最近的贫困户历史记录
	 * @param fid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public PoorFamilyHistory getLatestPoorFamilyHistory(Long fid) {
		List<PoorFamilyHistory> result = getCurrentSession().createQuery(" from PoorFamilyHistory as pfh where originId = :fid order by id desc")
			.setParameter("fid", fid)
			.list();
		return Collections.isEmpty(result) ? null : result.get(0);
	}


	/**
	 * 获取指定id的贫困户历史记录
	 * @param id
	 * @return
	 */
	@Override
	public PoorFamilyHistory getPoorFamilyHistory(Long id) {
		
		List result = getCurrentSession().createQuery(" from PoorFamilyHistory where id =" + id).list();
		return (PoorFamilyHistory) (Collections.isEmpty(result) ? null :result.get(0) );
	}


	@Override
	public void listPoorFamily(StringBuffer sql) {
	}

	@Override
	@SuppressWarnings("unchecked")
	public Pager<Map<String,Object>> listPoorFamily(String sql, String countSql,Integer pageNo, Integer pageSize) throws Exception {
		
		pageNo =  pageNo == null || pageNo < 1 ? 1 : pageNo;
		int pageStart = (pageNo - 1) * pageSize;
		pageSize =  pageSize == null || pageSize < 1 ? 12 : pageSize;
		
		List<Map<String,Object>> list = getCurrentSession().createSQLQuery(sql).setFirstResult(pageStart).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		Integer rows = ((BigInteger)getCurrentSession().createSQLQuery(countSql).uniqueResult()).intValue();
		return new Pager<Map<String,Object>>(pageSize, pageNo, rows, list);
	}


	@Override
	public List<Object> listPoorFamilyCondition(String familyCondition) {
		return getCurrentSession().createSQLQuery(familyCondition).list();
	}


	/**
	 * 证件号重复记录统计
	 * @return
	 */
	@Override
	@SuppressWarnings("all")
	public Pager<?> complexQueryDuplicateIDStatistics(Integer pageNo, Integer pageSize, final String year, String country, McUser user) {
		Map condition = new HashMap<>();
		
		getCurrentSession().doWork(new Work() {
			@Override
			public void execute(Connection connection) throws SQLException {
				connection.prepareStatement("set @@autocommit=1").execute();
				PreparedStatement ps = connection.prepareStatement("create temporary table if not exists duplicate_id_number as "
					+ "(select id_number,annual from family as f where f.is_del='0' and fid is not null and annual=? group by annual,f.id_number having count(id_number) > 1) ");
				ps.setString(1, StringUtils.isNotBlank(year) ? year : ApplicationProperties.getAnnual());
				ps.execute();
			}
		});
		
		String base = "from poor_family as pf, family as f,area as a,duplicate_id_number as t1 "
			+ " where pf.is_del='0' and f.is_del='0' and f.fid=pf.id and t1.annual=f.annual and f.annual=pf.annual and pf.annual= :annual and t1.id_number = f.id_number and a.id = pf.area_id ";
		
		String sql = "";
		if(StringUtils.isNotBlank(country)) {
			base += " and a.fatherId= (select id from area where areaCode= :areaCode) group by a.id ";
			sql = "select a1.area as region ,ifnull(t.povertyVillages,0) as povertyVillages from area as a1 left join (select a.area as region ,a.id as areaID,a.fatherId as fatherId ,count(*) as povertyVillages " + base +") as t on a1.id=t.areaID where a1.fatherId=(select id from area where areaCode= :areaCode)" ;
			condition.put("areaCode", country);
		} else {
			base += "group by a.fatherId ";
			sql = "select a.area as region,ifnull(t.povertyVillages,0) as povertyVillages from area as a left join (select (select area from area where id = a.fatherId) as region ,count(*) as povertyVillages " + base + ") as t on a.area = t.region where a.fatherId=3";
		}

		String count = " select count(*) from (" + sql +") as tCount " ;
		condition.put("annual", StringUtils.isNotBlank(year) ? year : ApplicationProperties.getAnnual());
		
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		int pageStart = (pageNo - 1) * pageSize;
		List<Map> result = getCurrentSession().createSQLQuery(sql)
			.setProperties(condition).setFirstResult(pageStart).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		
		BigInteger rows = (BigInteger)getCurrentSession().createSQLQuery(count).setProperties(condition).uniqueResult();
		
		Pager pager = new Pager<Map>(pageSize, pageNo,rows.intValue(), result);
		dropTempTable("duplicate_id_number");
		return pager;
	}

	/**
	 * 导入时的贫困户保存
	 */
    @Override
    public Long importPoorFamily(PoorFamily poorFamily) {
        Session session = getCurrentSession().getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        Long id = (Long) session.save(poorFamily);
        transaction.commit();
        session.close();
        return id;

    }


	@Override
	public int getCount(String sql, Map map) {
		BigInteger rows = (BigInteger)getCurrentSession().createSQLQuery(sql).setProperties(map).uniqueResult();
		return rows.intValue();
	}


	@Override
	public List<PoorFamilyVo> getPoorFamilyVo(Integer pageNo, Integer pageSize, String sql, Map map) {
		int pageStart = (pageNo - 1) * pageSize;
		List list = getCurrentSession().createSQLQuery(sql).setProperties(map).setFirstResult(pageStart).setMaxResults(pageSize).setResultTransformer(Transformers.aliasToBean(PoorFamilyVo.class)).list();
		return list;
	}
}
